package impl.repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dao.AccessDatabase;
import domain.Billing;
import domain.Category;
import domain.Customer;
import domain.Order;
import domain.Shipping;
import interfaces.repository.OrderRepository;

public class StubOrderRepositoryImpl implements OrderRepository {

	@Override
	public List<Order> findByNameCustomer(int idCustomer) {

		StubCustomerRepositoryImpl scri = new StubCustomerRepositoryImpl();
		StubBillingRepositoryImpl sbri = new StubBillingRepositoryImpl();
		StubShippingRepositoryImpl ssri = new StubShippingRepositoryImpl();
		

		List<Order> orders = new ArrayList<Order>();
		Order order;

		try {

			AccessDatabase a = new AccessDatabase();
			Connection con = a.conectar();
			Statement st = con.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT * FROM order where idCustomer='"
							+ idCustomer + "';");

			order = new Order(rs.getInt("idorder"), scri.findByID(rs
					.getInt("idCustomer")), rs.getDate("date"),
					rs.getFloat("subtotal"), rs.getFloat("totalBilledAmount"),
					ssri.findByID(rs.getInt("idShipping")),
					sbri.findById((rs.getInt("idBilling"))));

			orders.add(order);
			if(500<order.getSubTotal()&&order.getSubTotal()<1000){
				Customer oldCustomer = order.getCustomer();
				Customer newCustomer = new Customer(idCustomer, oldCustomer.getLogon(), oldCustomer.getName(), oldCustomer.getEmail(), oldCustomer.getPassword(), oldCustomer.getAddress(), Category.Regular.toString(), oldCustomer.getShipping(), oldCustomer.getBilling(), oldCustomer.getCart());
				scri.edit(oldCustomer, newCustomer);
			}if(1000<order.getSubTotal()){
				
				Customer oldCustomer = order.getCustomer();
				Customer newCustomer = new Customer(idCustomer, oldCustomer.getLogon(), oldCustomer.getName(), oldCustomer.getEmail(), oldCustomer.getPassword(), oldCustomer.getAddress(), Category.Premium.toString(), oldCustomer.getShipping(), oldCustomer.getBilling(), oldCustomer.getCart());
				scri.edit(oldCustomer, newCustomer);
				
			}
			con.close();
			return orders;

		} catch (SQLException ex) {
			return null;
		}
	}

	@Override
	public List<Order> findByValue(float max, float min) {

		StubCustomerRepositoryImpl scri = new StubCustomerRepositoryImpl();
		StubBillingRepositoryImpl sbri = new StubBillingRepositoryImpl();
		StubShippingRepositoryImpl ssri = new StubShippingRepositoryImpl();

		ArrayList<Order> orders = new ArrayList<Order>();
		Order order;

		try {

			AccessDatabase a = new AccessDatabase();
			Connection con = a.conectar();
			Statement st = con.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT * FROM order where value BETWEEN '"
							+ min + "' AND '" + max + "';");
			while (rs.next()) {
				order = new Order(rs.getInt("idorder"), scri.findByID(rs
						.getInt("idCustomer")), rs.getDate("date"),
						rs.getFloat("subtotal"),
						rs.getFloat("totalBilledAmount"), ssri.findByID(rs
								.getInt("idShipping")),
						sbri.findById(rs.getInt("idBilling")));

				orders.add(order);
			}
			con.close();
			return orders;
		} catch (SQLException ex) {
			return null;
		}

	}

	@Override
	public boolean add(Order order) {
		try {
			AccessDatabase a = new AccessDatabase();
			Connection con = a.conectar();
			Statement st = con.createStatement();
			st.execute("INSERT INTO 'order' ('id', 'customer', 'billing', 'shipping') VALUES ('"
					+ order.getId()
					+ "', '"
					+ order.getCustomer()
					+ "', '"
					+ order.getBilling() + "', '" + order.getShipping() + "');");
			con.close();
			return true;
		} catch (Exception t) {
			return false;
		}

	}

	@Override
	public List<Order> findByValueAndCustomer(Customer customer, float max,
			float min) {

		StubCustomerRepositoryImpl scri = new StubCustomerRepositoryImpl();
		StubBillingRepositoryImpl sbri = new StubBillingRepositoryImpl();
		StubShippingRepositoryImpl ssri = new StubShippingRepositoryImpl();

		ArrayList<Order> orders = new ArrayList<Order>();
		Order order;
		try {

			AccessDatabase a = new AccessDatabase();
			Connection con = a.conectar();
			Statement st = con.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT * FROM order where value BETWEEN '"
							+ min + "' AND '" + max + "';");

			while (rs.next()) {
				order = new Order(rs.getInt("idorder"), scri.findByID(rs
						.getInt("idCustomer")), rs.getDate("date"),
						rs.getFloat("subtotal"),
						rs.getFloat("totalBilledAmount"), ssri.findByID(rs
								.getInt("idShipping")),
						sbri.findById(rs.getInt("idBilling")));

				orders.add(order);
			}
			con.close();
			return orders;
		} catch (SQLException ex) {
			return null;
		}
	}

}
